package com.zlht.dao.imp;

import com.zlht.dao.BookDao;
import com.zlht.entity.Book;
import com.zlht.entity.Employee;
import com.zlht.entity.Store;
import com.zlht.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImp implements BookDao {
    @Override
    public List<Book> list() {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select b.id,b.name,a.name,b.price,b.publisher,b.pub_date,c.name from books b,author a,category c where b.author_id = a.id and b.category_id = c.id";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try{
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()){

                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String author = resultSet.getString(3);
                String price = resultSet.getString(4);
                String publisher= resultSet.getString(5);
                String date = resultSet.getString(6);
                String category = resultSet.getString(7);

                list.add(new Book(id,name,author,price,publisher,date,category));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,resultSet);
        }
        return list;
    }

    @Override
    public List<Book> search(String key, String value) {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select b.id,b.name,a.name,b.price,b.publisher,b.pub_date,c.name from books b,author a,category c where b.author_id = a.id and b.category_id = c.id and "+key+" like '%"+value+"%'";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Book> list = new ArrayList<>();
        try{
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            while (resultSet.next()){

                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String author = resultSet.getString(3);
                String price = resultSet.getString(4);
                String publisher= resultSet.getString(5);
                String date = resultSet.getString(6);
                String category = resultSet.getString(7);

                list.add(new Book(id,name,author,price,publisher,date,category));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,resultSet);
        }
        return list;
    }

    @Override
    public int save(Book book) {

        Connection connection = JDBCUtil.getConnection();
        String sql = "insert into books (name,author_id,price,publisher,pub_date,category_id) values (?,?,?,?,?,?)";
        PreparedStatement statement = null;
        Integer result = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1, book.getName());
            statement.setInt(2, book.getAuthor_id());
            statement.setString(3, book.getPrice());
            statement.setString(4, book.getPublisher());
            statement.setString(5, book.getDate());
            statement.setInt(6, book.getCategory_id());

            result = statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection, statement, null);
        }
        return result;
    }
    @Override
    public int update(Book book) {
        Connection connection = JDBCUtil.getConnection();
        String sql = "update books set name = ?,author_id = ?,price = ?,publisher = ?,pub_date = ?,category_id = ? where id = ?";
        PreparedStatement statement = null;
        Integer result = null;
        try{
            statement = connection.prepareStatement(sql);

            statement.setString(1, book.getName());
            statement.setInt(2, book.getAuthor_id());
            statement.setString(3, book.getPrice());
            statement.setString(4, book.getPublisher());
            statement.setString(5, book.getDate());
            statement.setInt(6, book.getCategory_id());
            statement.setInt(7, book.getId());

            result = statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,null);
        }
        return result;
    }

    @Override
    public int delete(Integer id) {
        Connection connection = JDBCUtil.getConnection();
        String sql = "delete from books where id = "+id;
        PreparedStatement statement = null;
        Integer result = null;
        try{
            statement = connection.prepareStatement(sql);

            result =statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection,statement,null);
        }
        return result;
    }
}
